<%-- 
    Document   : revenue
    Created on : Dec 8, 2011, 11:04:37 PM
    Author     : Michael
--%>

<%@page import="java.sql.*"%>
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>Revenue</title>
    </head>
    <body>
        <%
            Connection conn = null;
            try
            {
                conn = new DBClasses.GetConnection().getConnection();
                String query = "SELECT a.ItemName, SUM(s.NumUnits*a.UnitPrice) AS Sum " +
                                "FROM advertisement a, sales s " +
                                "WHERE s.AdID=a.AdvertisementID GROUP BY a.ItemName";
                ResultSet namers = conn.createStatement().executeQuery(query);
                query = "SELECT a.ItemType, SUM(s.NumUnits*a.UnitPrice) AS Sum " +
                                "FROM advertisement a, sales s " +
                                "WHERE s.AdID=a.AdvertisementID GROUP BY a.ItemType";
                ResultSet typers = conn.createStatement().executeQuery(query);
                query = "SELECT Concat(c.Firstname, ' ', c.Lastname) AS Name, SUM(s.NumUnits*a.UnitPrice) AS Sum " +
                                "FROM advertisement a, sales s, customer c " +
                                "WHERE s.AdID=a.AdvertisementID AND c.AccountNo=s.AccountNo GROUP BY Name";
                ResultSet custrs = conn.createStatement().executeQuery(query);
              %>  
<input type="button" value="Home" onclick="window.location='/TheLoop/gohome.jsp'"/>&nbsp;
          <input type="button" value="Log Out" onclick="window.location='/TheLoop/logout.jsp'"/>
        <h1>Revenue Charts</h1>
        <br />
        <h3>By Item Name</h3>
        <table width="400" name="itemtable" border="1">
            <tr>
                <td>Item Name</td>
                <td>Revenue</td>
            </tr>
            
            <% while(namers.next()) { %>
            <tr>
                <td><%=namers.getString("a.ItemName")%></td>
                <td>$<%=namers.getInt("Sum")%></td>
            </tr><% } %> 
        </table>
        
        <br />
        <h3>By Item Type</h3>
        <table width="400" name="itemtable" border="1">
            <tr>
                <td>Item Type</td>
                <td>Revenue</td>
            </tr>
            
            <% while(typers.next()) { %>
            <tr>
                <td><%=typers.getString("a.ItemType")%></td>
                <td>$<%=typers.getInt("Sum")%></td>
            </tr><% } %>
            </table>
         
      <br />
      <h3>By Customer</h3>
      <table width="400" name="itemtable" border="1">      
            <tr>
                <td>Customer Name</td>
                <td>Revenue</td>
            </tr>
            
            <% while(custrs.next()) { %>
            <tr>
                <td><%=custrs.getString("Name")%></td>
                <td>$<%=custrs.getInt("Sum")%></td>
            </tr><% } %>
            </table>
      <% 
       
       conn.createStatement().execute("CREATE VIEW CustomerTotals AS " +
                    "SELECT Concat(c.Firstname, ' ', c.Lastname) as Fullname, SUM(UnitPrice*NumUnits) AS Total " +
                    "FROM advertisement a, sales s, customer c " +
                    "WHERE a.AdvertisementID=s.AdId AND s.AccountNo=c.AccountNo " +
                    "GROUP BY c.CustomerID");
     
     
       ResultSet rs = conn.createStatement().executeQuery("SELECT Fullname, MAX(Total) AS MTotal FROM CustomerTotals");
       rs.next();
       %>    
         
      <br />
      <h3>Top Grossing Customer</h3>
      <table width="400" name="itemtable" border="1">      
            <td><%=rs.getString("Fullname")%></td>
            <td>$<%=rs.getInt("MTotal")%> </td>
      </table>
             <% 
       conn.createStatement().execute("DROP VIEW CustomerTotals");
       
       conn.createStatement().execute("CREATE VIEW EmployeeTotals AS " +
                "SELECT Concat(e.Firstname, ' ', e.Lastname) AS Fullname, SUM(UnitPrice*NumUnits) AS Total " +
                "FROM employees e, advertisement a, sales s " +
                "WHERE a.AdvertisementID=s.AdId AND a.Employee=e.EmployeeID " +
                "GROUP BY e.EmployeeID");
        
       rs = conn.createStatement().executeQuery("SELECT Fullname, MAX(Total) AS MTotal FROM EmployeeTotals");
       rs.next();
        %>  
      <br />
      <h3>Top Grossing Customer Rep</h3>
      <table width="400" name="itemtable" border="1">      
            <tr>
                <td><%=rs.getString("Fullname")%></td>
                <td>$<%=rs.getInt("MTotal")%> </td>
            </tr>
      </table>    
                
         <%    conn.createStatement().execute("DROP VIEW EmployeeTotals");   
            }
            catch(Exception e)
            {
                e.printStackTrace();
            }
            finally
                                       {
                try { conn.close();
                }
                catch(Exception e) {
                    e.printStackTrace();
                    response.sendRedirect("/TheLoop/gohome.jsp");
                }
            }
        %>
    </body>
</html>
